package net.versiforma.libraryreminder;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;


public class UserController {
		
	private String dbConnector;
	private String dbUsername;
	private String dbPassword;
	
	
	public UserController(Properties properties) {
		this.dbConnector = properties.getProperty("db_connector");
		this.dbUsername = properties.getProperty("db_username");
		this.dbPassword = properties.getProperty("db_password");
	}




	public  ArrayList<User> getUsers() throws SQLException {

		Connection dbConnection = DriverManager.getConnection(dbConnector, dbUsername, dbPassword);
		Statement stmt = dbConnection.createStatement();

		
		// Get users with each with their uid and email
		ArrayList<User> users = new ArrayList<User>();
		ResultSet rsu = stmt.executeQuery("SELECT uid, mail, name FROM dru_users WHERE status = 1");
		try {
			while (rsu.next()) {
				User u = new User();
				users.add(u);
				u.setUid(rsu.getString("uid"));
				u.setEmail(rsu.getString("mail"));
				u.setName(rsu.getString("name"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		// Get implemented libraries (from dru_profile_fields)
		ArrayList<DrupalImplementedLibrary> implementedLibraries = new ArrayList<DrupalImplementedLibrary>();
		ResultSet rsl = stmt.executeQuery("SELECT fid, name, category FROM dru_profile_fields");
		try {
			resultSet:
				while (rsl.next()) {
					String category = rsl.getString("category");
					String fieldName = rsl.getString("name");
					String fieldId = rsl.getString("fid");
//					System.out.println(category + " " + fieldName + " " + fieldId);


					Boolean found = false;
					while (!found) {
						for (DrupalImplementedLibrary library:implementedLibraries) {
							// Adds all the fieldId if matching library's name / category
							if (library.getName().equals(category)) {
								if		(fieldName.matches(".*active$"))		library.setActiveFieldId(fieldId);
								else if	(fieldName.matches(".*userid$"))		library.setUserIdFiledId(fieldId);
								else if	(fieldName.matches(".*password$"))		library.setPasswordFieldId(fieldId);
								else if	(fieldName.matches(".*threshold$"))		library.setThresholdFieldId(fieldId);
								else
									throw new Exception("Unknown profile field name " + fieldName);
								found = true;
								continue resultSet;
							}
						}
						// If no library with the category from the current resultSet's line found add this library 
						implementedLibraries.add(new DrupalImplementedLibrary(category));
					}
				}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		// populate users' library accounts
		for (User u:users) {
			ArrayList<LibraryAccount> accounts = new ArrayList<LibraryAccount>();
			for (DrupalImplementedLibrary library:implementedLibraries) {
				// Get user's account details for the library
				String query = 
					"SELECT " +
						"group_concat(if(fid = " + library.getActiveFieldId() + ", value, null)) AS active, " +
						"group_concat(if(fid = " + library.getUserIdFiledId() + ", value, null)) AS userid, " +
						"group_concat(if(fid = " + library.getPasswordFieldId() + ", value, null)) AS password, " +
						"group_concat(if(fid = " + library.getThresholdFieldId() + ", value, null)) AS threshold " +
					"FROM " +
						"dru_users, dru_profile_values " +
					"WHERE " +
						"dru_profile_values.uid = dru_users.uid AND dru_users.uid = " + u.getUid();
				ResultSet rs = stmt.executeQuery(query);
				try {
					// populating current library account, while loop should happen only once 
					while (rs.next()) {
						if ( rs.getString("active") == null || !rs.getString("active").equals("1"))
							continue;
						
						
						/*
						 * TODO
						 * Allow multiple accounts at the same library for a single user.
						 * The fields userid and password are expected to be split by double colon ("::").
						 */
						
						String[] userids = rs.getString("userid").split("::");
						String[] passwords = rs.getString("password").split("::");
						
						for (int i=0; i<userids.length; i++) {
							LibraryAccount account = new LibraryAccount();
							account.setLibraryName(library.getName());
							account.setActive(rs.getString("active"));
							account.setUserid(userids[i]);
							account.setPassword(passwords[i]);
							account.setSoonThresholdDays(rs.getString("threshold"));
							accounts.add(account);
						}
						
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (accounts.size() != 0) {
				u.setAccounts(accounts);
				System.out.println("user " + u.getUid() + " has " + u.getAccounts().size() + " accounts ");
			}
		}
		
		dbConnection.close();
		
		// Only users with accounts will be returned
		ArrayList<User> usersWithAccounts = new ArrayList<User>();
		for (User user:users) {
			if (user.getAccounts().size() > 0) usersWithAccounts.add(user);
		}
		
		for (User u:usersWithAccounts) {
			System.out.println("User: " + u.getUid() + " " + u.getEmail() );
			for (LibraryAccount a:u.getAccounts()) {
				System.out.println("\tname: " + a.getLibraryName());
				System.out.println("\tuid: " + a.getUserid());
				System.out.println("\tpassword: " + a.getPassword());
				System.out.println("\tthreshold: " + a.getSoonThresholdDays());
			}
		}
		
		return usersWithAccounts;
	}

}